Data access without looping — the FIND statement
In addition to all of these ways to retrieve and iterate through a set of related records, Progress has a very powerful way to retrieve single records without needing a query or result set definition of any kind. This is the FIND statement.
The FIND statement uses this basic syntax:
Using the FIND statement to fetch a single record from the database is pretty straightforward. This statement reads the first Customer and makes it available to the procedure:
This statement fetches the first Customer in New Hampshire:
It gets more interesting when you FIND the NEXT record or the PREV record. This should immediately lead you to the question: NEXT or PREV relative to what? Even the FIND FIRST statement has to pick a sequence of Customers in which one of them is first. Although it might seem intuitively obvious that Customer 1 is the first Customer, given that the Customers have an integer key identifier, this is the record you get back only because the CustNum index is the primary index for the table (you could verify this by looking in the Data Dictionary). Without any other instructions to go on, and with no WHERE clause to make it use another index, the FIND statement uses the primary index. You can use the USE-INDEX syntax to force Progress to use a particular index.
If you include a WHERE clause, Progress chooses one or more indexes to optimize locating the record. This might have very counter-intuitive results. For example, here’s a simple procedure with a FIND statement:
Figure 6–15 shows the expected result.
You can see that Customer 1 is in the USA. Here’s a variation of the procedure:
Figure 6–16 shows the not-so-expected result.
What happened here? If Customer 1 is the first Customer, and Customer 1 is in the USA, then why isn’t it the first Customer in the USA? Progress uses an index in the Country field to locate the first Customer in the USA, because that’s the most efficient way to find it. That index, called the CountryPost index, has the PostalCode as its secondary field. If you rerun this procedure again and ask to see the PostalCode field instead of the Name field, you’ll see why it came up first using that index, as shown in Figure 6–17.
The PostalCode is blank for this Customer, so it sorts first. Even if there is no other field in the index at all, that would only mean that the order of Customers within that index for a given country value would be undetermined. Only if the CustNum field is the next index component could you be sure that Customer 1 would come back as the first Customer in the USA.
These examples show that you must be careful when using any of the positional keywords (FIRST, NEXT, PREV, and LAST) in a FIND statement to make sure you know how the table is navigated.
Index cursors
To understand better how Progress navigates through a set of data, you need to understand the concept of index cursors. When you retrieve a record from the database using any of the statements you’ve seen in this chapter, Progress keeps track of the current record position using an index cursor—a pointer to the record, using the location in the database indexes of the key value used for retrieval.
When you execute the statement FIND FIRST Customer, for example, Progress sets a pointer to the record for Customer 1 within the CustNum index. If you execute the statement FIND FIRST Customer WHERE Country = “USA”, Progress points to Customer 1025 through the CountryPost index.
When you execute another FIND statement on the same table using one of the directional keywords, Progress can go off in any direction from the current index cursor location, depending on the nature of the statement. By default, it reverts to the primary index. Here’s an example that extends the previous one slightly:
Using the FIND statement in a REPEAT block
Notice the use of the REPEAT block to cycle through the remaining Customers. Within that block, you must write a FIND statement to get the next Customer because the REPEAT block itself, unlike the FOR EACH block, does not do the navigation for you. Also, the REPEAT block does not automatically terminate when the end of the Customers is reached, so you need to program the block with these three actions:
- You must do the
FINDwith theNO-ERRORqualifier at the end of the statement. This suppresses the error message that you would ordinarily get when there is no next Customer. - You must use the
AVAILABLEkeyword to check for the presence of a Customer and display fields only if it evaluates to true. - You must write an
ELSEstatement to match theIF-THENstatement, to leave the block when there is no Customer available. Otherwise, your block goes into an infinite loop when it reaches the end of the Customer records. And notice that this truly is a separate statement. TheIF-THENstatement ends with a period and theELSEkeyword begins a statement of its own.
All of these are actions that the FOR EACH block does for you as it reads through the set of Customers. In the REPEAT block, though, where you’re doing your own navigation, you need to do these things yourself.
Remember also that the REPEAT block scopes the statements inside the block to its own frame, unless you tell it otherwise. Therefore, you get one frame for the FIRST Customer and a new frame for all the Customer records retrieved within the REPEAT block.
The keyword AVAILABLE is a Progress 4GL built-in function, so its one argument properly belongs in parentheses, as in IF AVAILABLE (Customer). However, to promote the readability of the 4GL statement, the syntax also accepts the form as if it were a phrase without the parentheses, as in IF AVAILABLE Customer. This alternative is not generally available with other built-in functions.
Finally, the FORMAT “X(20)” phrase reduces the display size of the Name field from its default (defined in the Data Dictionary) of 30 characters, to make room for the PostalCode field.
Switching indexes between FIND statements
So what Customer do you expect to see as the next Customer after retrieving the first Customer using the CountryPost index (because of the WHERE clause)? If you remember that the default is always to revert to the primary index, then the result shown in Figure 6–18 should be clear.
Looking at the sequence of records displayed in the frame for the REPEAT block, it’s clear that Progress is using the primary index (the CustNum index) to navigate through the records. This is unaffected by the fact that the initial FIND was done using the CountryPost index, because of its WHERE clause.
What if you want to continue retrieving only Customers in the USA? In this case, you need to repeat the WHERE clause in the FIND statement in the REPEAT block:
Each FIND statement is independent of any other FIND statement, even if it refers to the same table, so the WHERE clause does not carry over automatically. If you do this, then Progress continues to use the CountryPost index for the retrieval, as the output in Figure 6–19 shows.
Because the PostalCode is the second field in the index used, the remaining records come out in PostalCode order.
Using a USE-INDEX phrase to force index selection
You can also force a retrieval sequence with the USE-INDEX phrase. For instance, if you want to find the next set of Customers based on the Customer name, you can use the Name index, which contains just that one field:
The output shown in Figure 6–20 confirms that Progress is walking through the records in Name order, starting with the name of the first Customer in the USA.
This technique can be very valuable in expressing your business logic in your procedures. You might need to identify a record based on one characteristic and then retrieve all other records (or perhaps just one additional record) based on some other characteristic of the record you first retrieved. This is one of the most powerful ways in which Progress lets you define your business logic without the overhead and cumbersome syntax required to deal with all data access in terms of sets.
Doing a unique FIND to retrieve a single record
Very often you just need to retrieve a single record using selection criteria that identify it uniquely. In this case, you can use a FIND statement with no directional qualifier. For example, you can identify a Customer by its Customer number. This is a unique value, so you can use the following FIND statement:
Figure 6–21 shows the result.
You need to be sure when you do this that only one record satisfies the selection criteria. Otherwise, you get an error at run time.
There’s also a shorthand for this FIND statement:
You can use this shorthand form if the primary index is a unique index (with no duplication of values), the primary index contains just a single field, and you want to retrieve a record using just that field. You can only use this form when all these conditions are true, so it’s not likely to be one you use frequently. Also, this shorthand form makes it harder to determine your criteria. It can break due to changes to the data definitions (for example, if someone went in and added another field to the CustNum index), so it’s better to be more specific and use a WHERE clause to identify the record.
Using the CAN-FIND function
Often you need to verify the existence of a record without retrieving it for display or update. For example, your logic might need to identify each Customer that has at least one Order, but you might not care about retrieving any actual Orders. To do this, you can use an alternative to the FIND statement that is more efficient because it only checks index entries wherever possible to determine whether a record exists, without going to the extra work of retrieving the record itself. This alternative is the CAN-FIND built-in function. CAN-FIND takes a single parameter, which can be any record selection phrase. The CAN-FIND function returns true or false depending on whether the record selection phrase identifies exactly one record in the database.
For example, imagine that you want to identify all Customers that placed Orders as early as 1997. You don’t need to retrieve or display the Orders themselves, you just need to know which Customers satisfy this selection criterion. Here’s a simple procedure that does this:
This procedure uses a little display trick you haven’t seen before. If the Customer has any Orders for 1997, then the procedure displays the Customer name. Otherwise, it displays the text phrase No 1997 Orders. If you include that literal value in the DISPLAY statement, it displays in its own column as if it were a field or a variable. To display it in place of the Name field, use the at-sign symbol (@). Figure 6–22 shows the result.
The CAN-FIND function takes the argument FIRST Order OF Customer WHERE OrderData < 1/1/98. Why is the FIRST keyword necessary? The CAN-FIND function returns true only if exactly one record satisfies the selection criteria. If there’s more than one match, then it returns false—without error—just as it would if there was no match at all. For example, if you remove the FIRST keyword from the example procedure and change the literal text to be No unique 1997 Order, and rerun it, then you see that most Customers have more than one Order placed in 1997:
After you page through the results, you see just a few records that don’t satisfy the criteria, as shown in Figure 6–23.
Because you don’t get an error if there’s more than one match, it’s especially important to remember to define your selection criteria so that they identify exactly one record when you want the function to return true.
The CAN-FIND function is more efficient than the FIND statement because it does not actually retrieve the database record. If the selection criteria can be satisfied just by looking at values in an index, then it doesn’t look at the field values in the database at all. However, this means that the record referenced in the CAN-FIND statement is not available to your procedure. For example, this variation on the example tries to display the OrderDate from the Order record as well as the Customer fields:
This results in the error shown in Figure 6–24, because the Order record is not available following the CAN-FIND reference to it.
If you need the Order record itself then you must use a form that returns it to you:
When you run this code, you see the OrderDate as well as the Customer fields except in those cases where there is no Order from 1997, as shown in Figure 6–25.
The samples so far have shown the CAN-FIND function in an IF-THEN statement. You can also use it anywhere where a logical (true/false) expression is valid in a WHERE clause, such as this:
The next chapter continues the discussion on building complex procedures, with details on record buffers and record scope.